﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spWH_LogOverview]
#-- Purpose:		Used for dashboard drilldowns on Maintenance Data
#--	Last Update:	09/03/2014
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spWH_LogOverview]
(
	@level_of_detail		varchar(20)			= 'Month',		-- All, Year, Quarter, Month, Week, Day
	@date_filter			datetime			= NULL,
	@include_weekends		bit					= 0
)
AS

--- Declare Local Variables
DECLARE		@dtStart datetime, @dtEnd datetime, @sSQL nvarchar(MAX)


--- Initialize Local Variables
IF @include_weekends IS NULL
	SET @include_weekends = 1

IF @date_filter IS NULL OR ISDATE(@date_filter) = 0
	SELECT @date_filter = CAST(CONVERT(varchar(20), GETDATE(), 112) as datetime)

IF @level_of_detail = 'All'
	SELECT		@dtStart = MIN([mnt_action_date]),
				@dtEnd = MAX([mnt_action_date])
	FROM		[dbo].[vwWH_LogOverview_Details]
ELSE IF @level_of_detail = 'Year'
	SELECT		@dtStart = DATEADD(year, DATEDIFF(year, 0, @date_filter), 0),
				@dtEnd = DATEADD(millisecond, -2, DATEADD(year, DATEDIFF(year, 0, @date_filter) + 1, 0))
ELSE IF @level_of_detail = 'Quarter'
	SELECT		@dtStart = DATEADD(quarter, DATEDIFF(quarter, 0, @date_filter), 0),
				@dtEnd = DATEADD(millisecond, -2, DATEADD(quarter, DATEDIFF(quarter, 0, @date_filter) + 1, 0))
ELSE IF @level_of_detail = 'Month'
	SELECT		@dtStart = DATEADD(month, DATEDIFF(month, 0, @date_filter), 0),
				@dtEnd = DATEADD(millisecond, -2, DATEADD(month, DATEDIFF(month, 0, @date_filter) + 1, 0))
ELSE IF @level_of_detail = 'Week'
	SELECT		@dtStart = DATEADD(day, -1 * (DATEPART(weekday, @date_filter) - 1), @date_filter),
				@dtEnd = DATEADD(millisecond, -2, DATEADD(day, (8 - DATEPART(weekday, @date_filter)), @date_filter))
ELSE IF @level_of_detail = 'Day'
	SELECT		@dtStart = DATEADD(day, DATEDIFF(day, 0, @date_filter), 0),
				@dtEnd = DATEADD(millisecond, -2, DATEADD(day, DATEDIFF(day, 0, @date_filter) + 1, 0))
ELSE
  BEGIN
	RAISERROR('Bad parameter values', 16, 1)
	RETURN
  END


--- Return the data requested
SELECT		@sSQL = 'SELECT		[ssd_sqlserver_name_ro],
			CAST(CASE WHEN DATEPART(weekday, [mnt_action_date]) < 6 THEN 0 ELSE 1 END as bit) [is_weekend],
			CONVERT(varchar(7), [mnt_action_date], 120) [year_month],
			SUM(CASE	WHEN [mnt_action_type] = ''index'' THEN [row_count]
						ELSE NULL
						END) [index_count],
			SUM(CASE	WHEN [mnt_action_type] = ''checkdb'' THEN [row_count]
						ELSE NULL
						END) [checkdb_count],
			SUM(CASE	WHEN [mnt_action_type] = ''restore'' AND [mnt_success] = 1 THEN [row_count]
						ELSE NULL
						END) [restore_succeeded_count],
			SUM(CASE	WHEN [mnt_action_type] = ''restore'' AND [mnt_success] = 0 THEN [row_count]
						ELSE NULL
						END) [restore_failed_count],
			SUM(CASE	WHEN [mnt_action_type] IN (''backup_log'', ''backup_diff'', ''backup_full'') THEN [row_count]
						ELSE NULL
						END) [backup_failed_count],
			SUM(CASE	WHEN [mnt_action_type] = ''backup_log'' THEN [row_count]
						ELSE NULL
						END) [backup_log_failed_count],
			SUM(CASE	WHEN [mnt_action_type] = ''backup_diff'' THEN [row_count]
						ELSE NULL
						END) [backup_diff_failed_count],
			SUM(CASE	WHEN [mnt_action_type] = ''backup_full'' THEN [row_count]
						ELSE NULL
						END) [backup_full_failed_count],
			SUM(CASE	WHEN [mnt_action_type] = ''update_stats'' THEN [row_count]
						ELSE NULL
						END) [update_stats_count],
			SUM(CASE	WHEN [mnt_action_type] = ''audit'' THEN [row_count]
						ELSE NULL
						END) [audit_count]
FROM		[dbo].[vwWH_LogOverview_Details]
WHERE		[mnt_action_date] BETWEEN @dStart AND @dEnd
			AND (
				@incWeekend = 1
				OR
				(@incWeekend = 0 AND DATEPART(weekday, [mnt_action_date]) < 6)
			)
GROUP BY	[ssd_sqlserver_name_ro],
			CAST(CASE WHEN DATEPART(weekday, [mnt_action_date]) < 6 THEN 0 ELSE 1 END as bit),
			CONVERT(varchar(7), [mnt_action_date], 120)
ORDER BY	[ssd_sqlserver_name_ro]'

EXEC		[sp_executesql]	@stmt = @sSQL,
							@params = N'@dStart datetime, @dEnd datetime, @incWeekend bit',
							@dStart = @dtStart,
							@dEnd = @dtEnd,
							@incWeekend = @include_weekends